package javaTask;
import static java.lang.System.out;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

public class jdbcTest1 {
    final String driver ="org.hsqldb.jdbcDriver";
    final String url="jdbc:hsqldb:hsql://localhost/";
    final String user="sa";
    final String pass="";
    private  Connection con;

    public static void main(String[] args) throws SQLException,Exception{
        jdbcTest1 demo =new jdbcTest1();
        demo.init();
        demo.menu();
    }
    public void menu() throws SQLException {
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        while(true) {
            System.out.println("==============银行账户管理系统==============");
            System.out.println("		1.管理员							");
            System.out.println("		2.用户							");
            System.out.println("========================================");
            System.out.print("请输入你的指令：");
            int command=sc.nextInt();
            switch(command) {
                case 1:
                    managerLogin();
                    break;
                case 2:
                    user();
                    break;
                default:
                    System.out.println("输入的指令不存在，请重新输入！");
            }
        }
    }
    public void managerLogin() throws SQLException {
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        System.out.println("==============管理员操作页面==============");
        System.out.print("请输入您的账号：");
        String account=sc.nextLine();
        if(checkAccount(account)==1) {
            System.out.print("请输入登录密码：");
            String code=sc.next();
            if(checkCode(code)==1) {
                managerMenu();
            }
            else {
                System.out.println("密码错误，请重新尝试！");
            }
        }
        else {
            System.out.println("账号错误，请重新尝试！");
        }
    }
    public int checkAccount(String account) throws SQLException {//检验账户
        int f=0;
        String sql="select * from manager where account = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setObject(1, account);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next()) {
            f++;
        }
        if(f==1) {
            return 1;
        }
        else {
            return 0;
        }
    }
    public int checkCode(String code) throws SQLException {
        int f=0;
        String sql="select * from manager where code = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setObject(1, code);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next()) {
            f++;
        }
        if(f==1) {
            return 1;
        }
        else {
            return 0;
        }
    }
    public void user() throws SQLException {
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        System.out.println("==============用户操作页面==============");
        System.out.println("		1.登录						");
        System.out.println("		2.注册						");
        System.out.println("		3.退出						");
        System.out.println("=====================================");
        System.out.print("请输入你的指令：");
        int command=sc.nextInt();
        switch(command) {
            case 1:
                userLogin();
                break;
            case 2:
                userRegister();
                break;
            default:
                System.out.println("输入的指令不存在，请重新输入！");
        }
    }
    public void userLogin() throws SQLException {
        String cardNum;
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        System.out.println("==============用户登录页面==============");
        System.out.print("请输入您的身份证号码：");
        cardNum=sc.nextLine();
        if(checkcardNum(cardNum)==1) {//检验身份证号码
            System.out.print("请输入登录密码：");
            String pwd=sc.nextLine();
            if(checkPwd(pwd)==1) {
                System.out.println("【欢迎"+getName(cardNum)+"女士/先生,"+"银行卡号为:"+getBankNum(cardNum)+"使用本系统！】");
                userMenu();
            }
            else {
                System.out.println("输入的密码错误！");
            }
        }
        else {
            System.out.println("输入的身份证号码错误！");
        }
    }
    private String getBankNum(String cardNum) throws SQLException {
        String bankNum=null;
        String sql="select * from user where cardNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setObject(1, cardNum);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next()) {
            bankNum=rs.getString(7);
        }
        return bankNum;
    }
    public void userRegister() throws SQLException {//注册
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        String sql="insert into user values(?,?,?,?,?,?,?,?)";
        PreparedStatement pstmt=con.prepareStatement(sql);
        System.out.print("请输入身份证号码：");
        String cardNum=sc.nextLine();
        if(checkcardNum(cardNum)==0) {
            System.out.print("请输入电话号码：");
            String tel=sc.nextLine();
            System.out.print("请设置密码：");
            String pwd=sc.nextLine();
            System.out.print("请输入电子邮箱：");
            String email=sc.nextLine();
            System.out.print("请输入真实姓名：");
            String relName=sc.nextLine();
            pstmt.setObject(1, cardNum);
            pstmt.setObject(2, tel);
            pstmt.setObject(3, pwd);
            pstmt.setObject(4, email);
            pstmt.setObject(5, 0);
            pstmt.setObject(6, relName);
            pstmt.setObject(7, "无");
            pstmt.setObject(8, "无");
            pstmt.execute();
        }
        else {
            System.out.println("【该身份证号码已经注册过账户，无法再注册！】");
        }
    }
    public String getName(String cardNum) throws SQLException {//获取姓名
        String name = null;
        String sql="select * from user where cardNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setObject(1, cardNum);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next()) {
            name=rs.getString(6);
        }
        return name;
    }
    public int checkPwd(String pwd) throws SQLException {//检验密码
        int flag=0;
        String sql="select * from user where pwd = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setObject(1, pwd);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next()) {
            flag++;
        }
        if(flag==1) {
            return 1;
        }
        else {
            return 0;
        }
    }
    public void userMenu() throws SQLException{
        while(true) {
            @SuppressWarnings("resource")
            Scanner sc=new Scanner(System.in);
            System.out.println("==============用户操作页面==============");
            System.out.println("		1.开户						");
            System.out.println("		2.存款						");
            System.out.println("		3.取款						");
            System.out.println("		4.转账						");
            System.out.println("		5.贷款						");
            System.out.println("		6.还款						");
            System.out.println("		7.修改个人信息					");
            System.out.println("		8.查看个人信息					");
            System.out.println("		9.注销						");
            System.out.println("		10.退出						");
            System.out.println("=====================================");
            System.out.print("请输入你的指令：");
            int command=sc.nextInt();
            switch(command) {
                case 1:
                    openAccount();//开户
                    break;
                case 2:
                    addMoney();//存款
                    break;
                case 3:
                    deleteMoney();//取款
                    break;
                case 4:
                    transferAccount();//转账
                    break;
                case 5:
                    loan();//贷款
                    break;
                case 6:
                    returnMoney();//还款
                    break;
                case 7:
                    correctUser();//修改个人信息
                    break;
                case 8:
                    userView();//查看信息（个人信息、贷款、交易记录）
                    break;
                case 9:
                    accountCancel();//注销账户
                    break;
                case 10:
                    return;
                default:
                    System.out.println("输入的指令不存在，请重新输入！");
            }
        }
    }
    public String getTime() {//获取取款、存款结束的时间
        Date d=new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM--dd HH:mm:ss");
        return sdf.format(d);
    }
    public double readInitialMoney(String bankNum) throws SQLException {
        double initialMoney = 0;//未存款前，银行卡中的金额
        String sql="select * from user where bankNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setObject(1, bankNum);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next()) {
            initialMoney=rs.getDouble(5);
            pstmt.execute();
        }
        return initialMoney;
    }
    public int checkcardNum(String cardNum) throws SQLException {//检查身份证号码是否正确
        int flag=0;
        String sql="select * from user where cardNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setObject(1, cardNum);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next()) {
            flag++;
        }
        if(flag==1) {
            return 1;
        }
        else {
            return 0;
        }
    }
    public int checkCheck(String bankNum) throws SQLException {//检查账户是否存在，检查银行卡号
        int flag=0;
        String sql="select * from user where bankNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setObject(1, bankNum);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next()) {
            flag++;
        }
        if(flag==1) {
            return 1;
        }
        else {
            return 0;
        }
    }
    public void openAccount() throws SQLException {//开户
        String sql="update user set bankNum = ? where cardNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        String sql2="update user set bankName = ? where cardNum = ?";
        PreparedStatement pstmt2=con.prepareStatement(sql2);
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        System.out.print("请输入您的身份证号码：");
        String cardNum=sc.nextLine();
        if(checkcardNum(cardNum)==1&&checkbankNum(cardNum)==1) {
            String bankNum=randomBankNum();
            System.out.println("开户成功，银行卡号码为："+bankNum);
            pstmt.setObject(1,bankNum);
            pstmt.setObject(2, cardNum);
            pstmt.execute();
            pstmt2.setObject(1, "中国农业银行");
            pstmt2.setObject(2, cardNum);
            pstmt2.execute();
        }
        else {
            System.out.println("【您已经存在对应的银行卡号码，无法再开户！】");
        }
    }
    public int checkbankNum(String cardNum) throws SQLException {//确定该身份证号码下没有任何的银行卡号码
        String bankNum=null;
        String sql="select*from user where cardNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setObject(1, cardNum);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next()) {
            bankNum=rs.getString(7);
        }
        if(bankNum.equals("无")) {
            return 1;
        }
        else {
            return 0;
        }
    }
    public String randomBankNum() throws SQLException{//产生12位的随机数作为用户的银行卡号
        Random r=new Random();
        while (true) {
            String cardId="";
            for (int i = 0; i <12 ; i++) {
                cardId+=r.nextInt(10);//0-9
            }
            if(checkCheck(cardId)==0){//卡号没有重复
                return cardId;
            }
            else{
                return null;
            }
        }
    }
    public void addMoney() throws SQLException {//存款    //先将钱数读取出来，再增加
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        double initialMoney = 0;//未存款前，银行卡中的金额
        double money = 0;
        String date = null;
        String bankNum = null;
        String type="存款";
        System.out.print("请输入金额存入的银行卡卡号：");
        bankNum=sc.nextLine();
        if(checkCheck(bankNum)==1) {
            initialMoney=readInitialMoney(bankNum);//读取银行卡中初始金额
            String sql="update user set money = ? where bankNum = ?";
            PreparedStatement pstmt=con.prepareStatement(sql);
            System.out.print("请输入存入的数额：");
            money=sc.nextDouble();
            initialMoney=money+initialMoney;
            pstmt.setObject(1, initialMoney);
            pstmt.setObject(2, bankNum);
            pstmt.execute();
            date=getTime();
            System.out.println("【"+money+"元已经存入"+bankNum+"账户中！"+"当前账户的余额为："+initialMoney+"元】");
            String t="无          ";
            //将此次交易记录存入
            recordTrade(bankNum,t,money,type,date,getCardNum(bankNum),t);
        }
        else {
            System.out.println("【您要存款的账户不存在，请先在本系统开户之后再存款！】");
        }
    }
    public void deleteMoney() throws SQLException {//取款
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        double initialMoney = 0;//未取款前，银行卡中的金额
        String type="取款";
        String bankNum = null;
        double money = 0;
        String date = null;
        System.out.print("请输入金额取出的银行卡卡号：");
        bankNum=sc.nextLine();
        if(checkCheck(bankNum)==1) {
            initialMoney=readInitialMoney(bankNum);
            String sql="update user set money = ? where bankNum = ?";
            PreparedStatement pstmt=con.prepareStatement(sql);
            System.out.print("请输入取出的金额：");
            money=sc.nextDouble();
            if(money<initialMoney) {//取出的金额不超过账户的余额
                initialMoney=initialMoney-money;
                pstmt.setObject(1, initialMoney);
                pstmt.setObject(2, bankNum);
                pstmt.execute();
                date=getTime();
                System.out.println("【"+money+"元已经从"+bankNum+"账户中"+"取出！"+"当前账户的余额为："+initialMoney+"元】");
                String t="无          ";
                //将此次交易记录存入
                recordTrade(t,bankNum,money,type,date,t,getCardNum(bankNum));
            }
            else {
                System.out.println("【银行卡中余额不足，无法取出！】");
            }
        }
        else {
            System.out.println("【您输入的银行卡号错误！】");
        }
    }
    public void transferAccount() throws SQLException {//转账
        String sql="update user set money = ? where bankNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        String sql2="update user set money = ? where bankNum = ?";
        PreparedStatement pstmt2=con.prepareStatement(sql2);
        double money1=0,money2=0;
        String date=null;
        String bankNum2 = null;
        String cardNum2 = null;
        String bankNum = null;
        double money = 0;
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        System.out.print("请输入您的银行卡号：");
        bankNum=sc.nextLine();
        if(checkCheck(bankNum)==1) {
            money1=readInitialMoney(bankNum);
            System.out.print("请输入转账的金额：");
            money=sc.nextDouble();
            if(money1>=money) {//账户中的钱足够转账
                @SuppressWarnings("resource")
                Scanner sc2=new Scanner(System.in);
                System.out.print("请输入对方的身份证号码：");
                cardNum2=sc2.nextLine();
                if(checkcardNum(cardNum2)==1) {
                    System.out.print("请输入对方的银行卡号：");
                    bankNum2=sc2.nextLine();
                    if(checkCheck(bankNum2)==1) {
                        money2=readInitialMoney(bankNum2);
                        money1=money1-money;
                        money2=money2+money;
                        pstmt.setObject(1, money1);
                        pstmt.setObject(2, bankNum);
                        pstmt2.setObject(1, money2);
                        pstmt2.setObject(2, bankNum2);
                        pstmt.execute();
                        pstmt2.execute();
                        date=getTime();
                        System.out.println("【"+getName(getCardNum(bankNum))+"女士/先生,"+money+"元从"+bankNum+"账户中转入"+bankNum2+"账户中】");
                        System.out.println("【"+bankNum+"账户的余额为："+money1+" && "+bankNum2+"账户的余额为："+money2+"】");
                        //记录这次交易
                        recordTrade(bankNum,bankNum2,money,"转账",date,getCardNum(bankNum),cardNum2);
                    }
                    else {
                        System.out.println("对方的银行卡号错误，请重新尝试！");
                    }
                }
                else {
                    System.out.println("输入错误，请重新尝试！");
                }
            }
            else {
                System.out.println("账户中余额不足，无法转账！");
            }
        }
        else {
            System.out.println("银行卡号输入错误，请重新尝试！");
        }
    }
    public void loan() throws SQLException {//贷款
        String style=null;
        double monthMoney=0;
        String sql="insert into loan values(?,?,?,?,?,?,?,?,?)";
        PreparedStatement pstmt=con.prepareStatement(sql);
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        System.out.println("--------------贷款操作页面--------------");
        System.out.print("请输入您的身份证号码：");
        String cardNum=sc.nextLine();
        if(checkcardNum(cardNum)==1) {
            System.out.print("请输入您的银行卡号码：");
            String bankNum=sc.nextLine();
            if(checkCheck(bankNum)==1) {
                @SuppressWarnings("resource")
                Scanner sc2=new Scanner(System.in);
                System.out.print("请输入您的贷款金额（万元为单位,2~200之间）：");
                double loanMoney=sc2.nextDouble();
                if(checkLoanMoney(loanMoney)==1) {
                    System.out.print("请输入您的贷款期限（以月为单位，3~36之间）：");
                    double loanTerm=sc2.nextDouble();
                    if(checkLoanTerm(loanTerm)==1) {
                        System.out.println("------还款方式如下------");
                        System.out.println("     1.等额本息法");
                        System.out.println("     2.等额本金法");
                        System.out.println("---------------------");
                        System.out.print("请输入您的选择：");
                        int command=sc.nextInt();
                        if(command==1) {
                            style="等额本息法";
                            monthMoney=(10000*loanMoney*0.006*Math.pow(1.006,loanTerm))/(Math.pow(1.006,loanTerm)-1);
                        }
                        else if(command==2) {
                            style="等额本金法";
                            monthMoney=(10000*loanMoney/loanTerm)+(loanMoney-getRepayment(bankNum))*0.006;
                        }
                        String date=getTime();
                        System.out.println("【贷款成功！】");
                        updateMoney(bankNum,loanMoney);//更新账户的余额
                        String type="贷款";
                        //将此次交易记录存入
                        recordTrade(bankNum,"中国农业银行",loanMoney*10000,type,date,getCardNum(bankNum),"中国农业银行");
                        //将信息存入表中
                        pstmt.setObject(1, cardNum);
                        pstmt.setObject(2, bankNum);
                        pstmt.setObject(3, loanMoney*10000);
                        pstmt.setObject(4, date);
                        pstmt.setObject(5, 0.006);
                        pstmt.setObject(6, loanTerm);
                        pstmt.setObject(7, monthMoney);
                        pstmt.setObject(8, 0.0);
                        pstmt.setObject(9, style);
                        pstmt.execute();
                    }
                }
                else {
                    System.out.println("您输入的金额不在银行贷款的范围之内，无法办理贷款业务！");
                }
            }
            else {
                System.out.println("银行卡号码错误或者不存在，请重新尝试！");
            }
        }
        else {
            System.out.println("身份证号码输入错误，请重新尝试！");
        }
    }
    public int checkLoanMoney(double money) {//检验用户贷款金额是否在银行借贷范围之后
        if(money>=2&&money<=200) {
            return 1;
        }
        else {
            return 0;
        }
    }
    public int checkLoanTerm(double loanTerm) {
        if(loanTerm>=3&&loanTerm<=36) {
            return 1;
        }
        else {
            return 0;
        }
    }
    public void updateMoney(String bankNum,double loanMoney) throws SQLException {//贷款之后，账户金额要增加
        double initialMoney=0;
        initialMoney=readInitialMoney(bankNum);//读取银行卡中初始金额
        String sql="update user set money = ? where bankNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        initialMoney=loanMoney*10000+initialMoney;
        pstmt.setObject(1, initialMoney);
        pstmt.setObject(2, bankNum);
        pstmt.execute();
        System.out.println("【贷款的"+loanMoney*10000+"元已经存入"+bankNum+"账户中！"+"当前账户的余额为："+initialMoney+"元】");
    }
    public void returnMoney() throws SQLException {//还款
        String sql="update loan set repayment = ? where cardNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        System.out.print("请输入您的身份证号码：");
        String cardNum=sc.nextLine();
        if(checkcardNum2(cardNum)==1) {
            System.out.print("请输入您的银行卡号码：");
            String bankNum=sc.nextLine();
            if(checkbankNum2(bankNum)==1) {//系统自动从用户的银行卡中扣除
                double initialMoney=readInitialMoney(bankNum);
                double monthMoney=getMonthMoney(bankNum);
                if(initialMoney>=monthMoney) {
                    pstmt.setObject(1,getMonthMoney(bankNum)+getRepayment(bankNum));
                    pstmt.setObject(2, cardNum);
                    pstmt.execute();
                    deletemoney(bankNum);
                    System.out.println("【还款成功！】");
                    String date=getTime();
                    //将此次交易记录存入
                    recordTrade(bankNum,"中国农业银行",getMonthMoney(bankNum),"还款",date,getCardNum(bankNum),"中国农业银行");
                }
                else {
                    System.out.println("账户余额不足，无法还款！");
                }
            }
            else {
                System.out.println("银行卡号码输入错误！");
            }
        }
        else {
            System.out.println("输入的身份证号码错误！");
        }
    }
    public int checkcardNum2(String cardNum) throws SQLException {//在loan表中检验身份证号码是否存在
        int flag=0;
        String sql="select * from loan where cardNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setObject(1, cardNum);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next()) {
            flag++;
        }
        if(flag==1) {
            return 1;
        }
        else {
            return 0;
        }
    }
    public int checkbankNum2(String bankNum) throws SQLException {//在loan。表中检验银行卡号码是否存在
        int flag=0;
        String sql="select * from loan where bankNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setObject(1, bankNum);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next()) {
            flag++;
        }
        if(flag==1) {
            return 1;
        }
        else {
            return 0;
        }
    }
    public double getMonthMoney(String bankNum) throws SQLException {//返回用户需要还款的金额
        double monthMoney=0.0;
        String sql="select * from loan where bankNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setObject(1, bankNum);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next()) {
            monthMoney=rs.getDouble(7);//double小数点后默认保留6位
        }
        return monthMoney;
    }
    public void deletemoney(String bankNum) throws SQLException {//还款成功时，该用户账户余额扣除还款费用
        String sql="update user set money = ? where bankNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        double initialMoney=readInitialMoney(bankNum);
        double monthMoney=getMonthMoney(bankNum);
        if(initialMoney>=monthMoney) {
            initialMoney=initialMoney-monthMoney;
            pstmt.setObject(1, initialMoney);
            pstmt.setObject(2, bankNum);
            pstmt.execute();
            System.out.println("【"+monthMoney+"元已经从"+bankNum+"账户中"+"扣除！"+"当前账户的余额为："+initialMoney+"元】");
        }
    }
    public double getRepayment(String bankNum) throws SQLException {//返回用户已还款数
        double initialRepayment=0.0;
        String sql="select * from loan where bankNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setObject(1, bankNum);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next()) {
            initialRepayment=rs.getDouble(8);
        }
        return initialRepayment;
    }
    public void recordTrade(String bankNum,String bankNum2,double money,String type,String date,String cardNum,String cardNum2) throws SQLException {//记录每一笔交易
        String sql="insert into movemoney values(?,?,?,?,?,?,?)";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setObject(1,bankNum);
        pstmt.setObject(2,bankNum2);
        pstmt.setObject(3,money);
        pstmt.setObject(4,type);
        pstmt.setObject(5,date);
        pstmt.setObject(6,cardNum);
        pstmt.setObject(7,cardNum2);
        pstmt.execute();
    }
    public String getCardNum(String bankNum) throws SQLException {//获取身份证号码
        String cardNum = null;
        String sql="select * from user where bankNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setObject(1, bankNum);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next()) {
            cardNum=rs.getString(1);
        }
        return cardNum;
    }
    public void userView() throws SQLException {//用户查看信息（个人信息、交易信息、贷款信息）
        while(true) {
            @SuppressWarnings("resource")
            Scanner sc=new Scanner(System.in);
            System.out.println("----------------用户查看信息----------------");
            System.out.println("		1.查看个人信息");
            System.out.println("		2.查看交易记录");
            System.out.println("		3.查看贷款信息");
            System.out.println("		4.退出");
            System.out.println("-----------------------------------------");
            System.out.print("请输入你要操作的指令：");
            int command=sc.nextInt();
            switch(command) {
                case 1:
                    findOwnInformation();
                    break;
                case 2:
                    findTradeInformation();
                    break;
                case 3:
                    findLoanInformation();
                    break;
                case 4:
                    return;
                default:
                    System.out.println("输入的指令不存在！");
            }
        }
    }
    public void findLoanInformation() throws SQLException {//查看贷款信息
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        String sql="select * from loan where bankNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        System.out.print("请输入您的银行卡号码：");
        String bankNum=sc.nextLine();
        if(checkbankNum2(bankNum)==1) {
            pstmt.setObject(1, bankNum);
            ResultSet rs=pstmt.executeQuery();
            System.out.println("--------------贷款信息如下--------------");
            out.printf("%-10s\t%-10s\t%-10s%-10s\t\t    %-10s\t%-10s%-10s\t%-10s\t\t%-10s\n", "身份证号码","银行卡号码","贷款金额","交易日期","月利率","贷款期限","每月还款金额","已还款金额","还款方式");
            while(rs.next()) {//自己方
                pstmt.execute();
                System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getDouble(3)+"\t    "+rs.getString(4)+"   "+rs.getString(5)+"\t"+rs.getString(6)+"\t"+rs.getString(7)+"\t"+rs.getString(8)+"\t"+rs.getString(9));
            }
        }
        else {
            System.out.println("您不存在贷款！");
        }
    }
    public void accountCancel() throws SQLException {//注销
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        String sql="delete from user where cardNum = ?";
        PreparedStatement pstmt =con.prepareStatement(sql);
        System.out.print("请输入要删除账户持有人的身份证号码：");
        String cardNum=sc.nextLine();
        if(check(cardNum)==0) {
            pstmt.setObject(1,cardNum);
            pstmt.execute();
            System.out.println("注销成功！");
        }
        else {
            System.out.println("【该账户余额不为0，无法注销！】");
        }
    }
    public int check(String cardNum) throws SQLException {//检查账户是否还有金额，如果有则无法注销
        int flag = 0;
        String sql="select * from user where cardNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setObject(1, cardNum);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next()) {
            double money;
            money=rs.getDouble(5);
            pstmt.execute();
            if(money>0) {
                flag=1;
            }
            else {
                flag=0;
            }
        }
        return flag;

    }
    public void managerMenu() throws SQLException {
        while(true) {
            @SuppressWarnings("resource")
            Scanner sc=new Scanner(System.in);
            System.out.println("===============管理员操作页面============");
            System.out.println("		1.增加账户信息					");//相当于开户
            System.out.println("		2.删除账户信息					");//相当于销户
            System.out.println("		3.查询账户信息					");
            System.out.println("		4.修改账户信息					");
            System.out.println("		5.显示统计信息					");//目前银行已有账户数、贷款账户数
            System.out.println("		6.浏览所有账户全部信息			");
            System.out.println("		7.退出						");
            System.out.println("=====================================");
            System.out.print("请输入你的指令：");
            int command=sc.nextInt();
            switch(command) {
                case 1:
                    addUser();
                    break;
                case 2:
                    deleteUser();
                    break;
                case 3:
                    findUser();
                    break;
                case 4:
                    correctUser();
                    break;
                case 5:
                    countView();
                    break;
                case 6:
                    userDisplay();
                    break;
                case 7:
                    return;
                default:
                    System.out.println("输入的指令不存在，请重新输入！");
            }
        }
    }
    public void init()throws Exception{//初始化
        if(con!=null)
            return;
        Class.forName(driver);
        con=DriverManager.getConnection(url,user,pass);
    }
    public void close() throws Exception{//关闭
        if(con!=null)
            con.close();
    }
    public static void println(String str){
        out.println(str);
    }
    public  void initTable()throws SQLException{
        String sql ="create table user(name varchar(30),cardNum varchar(30),tel varchar(30),pwd varchar(30),email varchar(30),money double,relName varchar(30))";
        Statement stmt =con.createStatement();
        stmt.executeUpdate(sql);
        stmt.close();
    }
    //增加的信息不能重复，要先检验数据表该信息是否已经存在
    public void addUser() throws SQLException {//增加账户信息
        String bankNum=randomBankNum();
        String sql="insert into user values(?,?,?,?,?,?,?,?)";
        PreparedStatement pstmt=con.prepareStatement(sql);
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        System.out.print("请输入身份证号码：");
        String cardNum=sc.nextLine();
        if(checkcardNum(cardNum)==0) {//检验该账户是否已经存在
            System.out.print("请输入电话号码：");
            String tel=sc.nextLine();
            System.out.print("请设置密码：");
            String pwd=sc.nextLine();
            System.out.print("请输入电子邮箱：");
            String email=sc.nextLine();
            System.out.print("请输入真实姓名：");
            String relName=sc.nextLine();
            pstmt.setObject(1, cardNum);
            pstmt.setObject(2, tel);
            pstmt.setObject(3, pwd);
            pstmt.setObject(4, email);
            pstmt.setObject(5, 0);
            pstmt.setObject(6, relName);
            pstmt.setObject(7, bankNum);
            pstmt.setObject(8, "中国农业银行");
            pstmt.execute();
            System.out.println("【"+relName+"女士/先生,银行卡号为："+bankNum+"添加成功！】");
        }
        else {
            System.out.println("【该身份证号码已经存在，无法添加下信息！】");
        }
    }
    public void deleteUser() throws SQLException {//删除账户信息
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        String sql="delete from user where cardNum = ?";
        PreparedStatement pstmt =con.prepareStatement(sql);
        System.out.print("请输入要删除账户持有人的身份证号码：");
        String cardNum=sc.nextLine();
        pstmt.setObject(1,cardNum);
        pstmt.execute();
        System.out.println("【  删除成功！  】");
    }
    public void findUser() throws SQLException {//查询账户信息
        while(true) {
            @SuppressWarnings("resource")
            Scanner sc=new Scanner(System.in);
            System.out.println("--------------查询账户信息操作页面--------------");
            System.out.println("1.查询账户持有者个人信息");
            System.out.println("2.查询账户持有者交易信息");
            System.out.println("3.退出");
            System.out.print("请输入你要操作的指令：");
            int command=sc.nextInt();
            switch(command) {
                case 1:
                    findOwnInformation();//查询个人信息
                    break;
                case 2:
                    findTradeInformation();//查询交易信息
                    break;
                case 3:
                    return;
                default:
                    System.out.println("输入的指令不存在，请重新输入！");
            }
        }
    }
    public void findOwnInformation() throws SQLException{//查询个人信息
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        String sql="select * from user where cardNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        System.out.print("请输入要查询账户持有者的身份证号码：");
        String cardNum=sc.nextLine();
        pstmt.setObject(1, cardNum);
        ResultSet rs=pstmt.executeQuery();
        while(rs.next()) {
            String cardNumber;
            String tel;
            String pwd;
            String email;
            double money;
            String relName;
            String bankNum;
            String bankName;
            cardNumber=rs.getString(1);
            tel=rs.getString(2);
            pwd=rs.getString(3);
            email=rs.getString(4);
            money=rs.getDouble(5);
            relName=rs.getString(6);
            bankNum=rs.getString(7);
            bankName=rs.getString(8);
            System.out.println("--------------查询的用户信息如下--------------");
            out.printf("%-10s\t%-10s  %-10s %-10s   %-10s %-10s%-10s  %-10s\n","身份证号码","电话","密码","邮箱","余额","真实姓名","银行卡号","开户银行");
            System.out.println(cardNumber+"\t"+tel+"  "+pwd+"  "+email+"  "+money+"\t   "+relName+"\t   "+bankNum+"\t  "+bankName);
            pstmt.execute();
        }
    }
    public void findTradeInformation() throws SQLException {//查询交易信息
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        String sql="select * from movemoney where ownbankNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        String sql2="select * from movemoney where otherbankNum = ?";
        PreparedStatement pstmt2=con.prepareStatement(sql2);
        System.out.print("请输入要查询账户持有者的身份证号码：");
        String cardNum=sc.nextLine();
        if(checkcardNum(cardNum)==1) {//应该在movemoney表中进行查询该用户是否存在
            System.out.print("请输入要查询账户持有者的银行卡号码：");
            String bankNum=sc.nextLine();
            if(checkCheck(bankNum)==1) {
                pstmt.setObject(1, bankNum);
                ResultSet rs=pstmt.executeQuery();
                System.out.println("--------------"+getName(cardNum)+"女士/先生的交易信息如下--------------");
                out.printf("%-10s\t%-10s\t\t%-10s%-10s  %-10s\t\t%-10s\t%-10s\n", "自己方","交易方","交易金额","交易类型","交易日期","身份证号码","交易方身份证号码");
                while(rs.next()) {//自己方
                    pstmt.execute();
                    System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t\t"+rs.getDouble(3)+"\t    "+rs.getString(4)+"\t\t"+rs.getString(5)+"\t"+rs.getString(6)+"\t\t"+rs.getString(7));
                }
                pstmt2.setObject(1, bankNum);
                ResultSet rs2=pstmt2.executeQuery();
                while(rs2.next()) {//交易方
                    pstmt2.execute();
                    System.out.println(rs2.getString(1)+"\t"+rs2.getString(2)+"\t\t"+rs2.getDouble(3)+"\t    "+rs2.getString(4)+"\t\t"+rs2.getString(5)+"\t"+rs2.getString(6)+"\t\t"+rs2.getString(7));
                }
            }
            else {
                System.out.println("输入的银行卡号不存在，请再次尝试！");
            }
        }
        else {
            System.out.println("输入的身份证号码不正确，请再次尝试！");
        }
    }
    public void correctUser() throws SQLException {//修改用户的账户信息
        while(true) {
            @SuppressWarnings("resource")
            Scanner sc=new Scanner(System.in);
            System.out.println("--------------修改账户信息操作页面--------------");
            System.out.println("1.修改联系电话");
            System.out.println("2.修改登录密码");
            System.out.println("3.修改邮箱地址");
            System.out.println("4.退出");
            System.out.print("请输入你要操作的指令：");
            int command=sc.nextInt();
            switch(command) {
                case 1:
                    correctTel();//修改电话
                    break;
                case 2:
                    correctPwd();//修改密码
                    break;
                case 3:
                    correctEmail();//修改邮箱
                    break;
                case 4:
                    return;
                default:
                    System.out.println("输入的指令不存在，请重新输入！");
            }
        }
    }
    public void correctTel() throws SQLException {//修改电话
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        String sql="update user set tel = ? where cardNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        System.out.print("请输入要修改人的身份证号码：");
        String cardNum=sc.nextLine();
        if(checkcardNum(cardNum)==1) {
            System.out.print("请输入更换的电话号码：");
            String tel=sc.nextLine();
            pstmt.setObject(1, tel);
            pstmt.setObject(2, cardNum);
            pstmt.execute();
            System.out.println("【电话号码修改成功！】");
        }
        else {
            System.out.println("输入的身份证号码不正确，请重新输入！");
            correctTel();
        }
    }
    public void correctPwd() throws SQLException {//修改登录密码
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        String sql="update user set pwd = ? where cardNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        System.out.print("请输入要修改人的身份证号码：");
        String cardNum=sc.nextLine();
        if(checkcardNum(cardNum)==1) {
            System.out.print("请输入修改后的密码：");
            String pwd=sc.nextLine();
            pstmt.setObject(1, pwd);
            pstmt.setObject(2, cardNum);
            pstmt.execute();
            System.out.println("【登录密码修改成功！】");
        }
        else {
            System.out.println("输入的身份证号码不正确，请重新输入！");
            correctPwd();
        }
    }
    public void correctEmail() throws SQLException {//修改电子邮箱
        @SuppressWarnings("resource")
        Scanner sc=new Scanner(System.in);
        String sql="update user set email = ? where cardNum = ?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        System.out.print("请输入要修改人的身份证号码：");
        String cardNum=sc.nextLine();
        if(checkcardNum(cardNum)==1) {
            System.out.print("请输入修改后的电子邮箱：");
            String email=sc.nextLine();
            pstmt.setObject(1, email);
            pstmt.setObject(2, cardNum);
            pstmt.execute();
            System.out.println("【电子邮箱修改成功！】");
        }
        else {
            System.out.println("输入的身份证号码不正确，请重新输入！");
            correctEmail();
        }
    }
    public void countView() throws SQLException {//显示统计信息
        while(true) {
            @SuppressWarnings("resource")
            Scanner sc=new Scanner(System.in);
            System.out.println("--------------显示统计信息操作页面--------------");
            System.out.println("1.统计目前银行中存在的账户数量");
            System.out.println("2.统计目前银行中贷款的账户数量");
            System.out.println("3.退出");
            System.out.print("请输入你要操作的指令：");
            int command=sc.nextInt();
            switch(command) {
                case 1:
                    accountNumber();//统计目前银行中存在的账户数量
                    break;
                case 2:
                    loanNumber();//目前银行中贷款的账户数量
                    break;
                case 3:
                    return;
                default:
                    System.out.println("输入的指令不存在，请重新输入！");
            }
        }
    }
    public void accountNumber() throws SQLException {//统计目前银行中存在的账户数量
        String sql="select * from user";
        PreparedStatement pstmt =con.prepareStatement(sql);
        ResultSet rs =pstmt.executeQuery();
        int cout = 0;
        while(rs.next()) {
            cout++;
        }
        System.out.println("目前银行中存在的账户数量为："+cout);
    }
    public void loanNumber() throws SQLException {//统计目前银行中贷款的账户数量
        String sql="select * from loan";
        PreparedStatement pstmt=con.prepareStatement(sql);
        //String type="贷款";
        //pstmt.setObject(1, type);
        ResultSet rs=pstmt.executeQuery();
        int number = 0;
        while(rs.next()) {
            number++;
        }
        System.out.println("目前银行中贷款的账户数量："+number);
    }
    public void userDisplay() throws SQLException{//将数据库中用户的信息全部输出
        String sql ="select* from user";
        PreparedStatement pstmt =con.prepareStatement(sql);
        ResultSet rs =pstmt.executeQuery();
        System.out.println("--------------------全部用户的个人信息如下--------------------");
        out.printf("%-10s\t%-10s  %-10s %-10s   %-10s\t%-10s%-10s  %-10s\n","身份证号码","电话","密码","邮箱","余额","真实姓名","银行卡号","开户银行");
        while (rs.next()){
            String cardNum=rs.getString("cardNum");
            String tel=rs.getString("tel");
            String pwd=rs.getString("pwd");
            String email=rs.getString("email");
            double money=rs.getDouble("money");
            String relName=rs.getString("relName");
            String bankNum=rs.getString("bankNum");
            String bankName=rs.getString("bankName");
            System.out.println(cardNum+"\t"+tel+"  "+pwd+"  "+email+"  "+money+"\t\t"+relName+"\t   "+bankNum+"\t   "+bankName);
        }
        String sql1="select * from movemoney";
        PreparedStatement pstmt1 =con.prepareStatement(sql1);
        ResultSet rs1 =pstmt1.executeQuery();
        System.out.println("--------------------全部用户的历史交易信息如下--------------------");
        out.printf("%-10s\t%-10s\t%-10s\t\t%-10s\t%-10s\t\t\t%-10s\t\t\t%-10s\n", "自己方","交易方","交易金额","交易类型","交易日期","身份证号码","交易方身份证号码");
        while(rs1.next()) {
            String outUser=rs1.getString("ownbankNum");
            String inUser=rs1.getString("otherbankNum");
            double money1=rs1.getDouble("money");
            String type=rs1.getString("type");
            String date=rs1.getString("date");
            String outcardNum=rs1.getString("owncardNum");
            String incardNum=rs1.getString("othercardNum");
            System.out.println(outUser+"\t"+inUser+"\t"+money1+"\t\t\t"+type+"\t\t"+date+"\t\t"+outcardNum+"\t\t\t"+incardNum);
        }
    }
}
